<?php
/**
*  Create On 2014-12-04
*  Author yiwei
*  QQ:1006629314
**/
class period extends Model{
	
	public function getPeriodById($id){
		$sql = 'SELECT `class_id`,`course`,`section`,`xdf_class`.`name` AS className,video_id,note_id FROM `xdf_period`
				LEFT JOIN `xdf_class` ON `xdf_class`.`id` = `xdf_period`.`class_id`
				WHERE `xdf_period`.`id` = '.$id;
		return $this->fetchRow($sql);
	}
	
	public function getPeriodByHomeworkId($homework_id){
		$sql="SELECT `id` as `period_id`,teacher_id FROM `xdf_period` WHERE `id` IN ( 
			SELECT `period_id` FROM `xdf_homework_period` 
			WHERE `homework_id`='$homework_id' AND `publish_type`='3');";
		return $this->fetchRow($sql);
	}
	/*学生查找课时
	 * 
	 * type : 0-仅包含课时信息,1-包含课时对应视频信息,2-包含课时对应作业信息,
	 * 			3-包含课时对应笔记信息,4-包含课时对应备课信息
	 */
	
	//视频与笔记联动，有视频一定有笔记
	public function searchStuPeriod($PageSize, $ListNum, $stu_id,$type,$start_date,$end_date,
					$class_id = null,$course = null,$teacher_id = null,$order = 0){
		$sql = 'SELECT `xdf_period`.`id` AS period_id,`xdf_period`.`class_id`,`xdf_period`.`teacher_id`,
				`xdf_period`.`subject_id`,`course`,`place`,`section`,`date`,`start_time`,`end_time`,
				`xdf_class`.`name` AS className,`xdf_class`.`section_amount`,`xdf_user`.`name` AS teacherName';
		
		if($type == 1){
			$sql .= ',`xdf_video`.`id` AS video_id,`xdf_video`.`time`';
			$sql .= ',`xdf_note`.`id` AS note_id,`xdf_note`.`amount`';
		}else if($type == 2){
			$sql .= ',`xdf_homework`.*,`xdf_homework_period`.`homework_id`';
		}else if($type == 3){
			$sql .= ',`xdf_note`.`id` AS note_id,`xdf_note`.`amount`';
		}
		else {
			$sql .= ',`xdf_homework_period`.`homework_id`,`video_id`,`xdf_period`.`note_id`,
					`xdf_video`.`name` AS videoName,`xdf_homework`.`name` AS homeworkName';
		}
		
		$sql .= ' FROM `xdf_class_student`
				JOIN `xdf_class` ON `xdf_class_student`.`student_id` = '.$stu_id.'
				AND `xdf_class`.`id` = `xdf_class_student`.`class_id`
				LEFT JOIN `xdf_period` ON `xdf_period`.`class_id` = `xdf_class`.`id`
				LEFT JOIN `xdf_user` ON `xdf_period`.`teacher_id`=`xdf_user`.`id` ';
		
		if($type == 1){
			$sql .= ' INNER JOIN `xdf_video` ON `xdf_video`.`id` = `xdf_period`.`video_id`';
			$sql .= ' INNER JOIN `xdf_note` ON `xdf_period`.`note_id` = `xdf_note`.`id`';
		}else if($type == 2){
			$sql .= ' INNER JOIN `xdf_homework_period` ON `xdf_homework_period`.`period_id` = `xdf_period`.`id`
					  AND `xdf_homework_period`.`publish_type` = 3
					  INNER JOIN `xdf_homework` ON `xdf_homework_period`.`homework_id` = `xdf_homework`.`id`';
		}else if($type == 3){
			$sql .= ' INNER JOIN `xdf_note` ON `xdf_period`.`note_id` = `xdf_note`.`id`';
		}
		else{
			$sql .= ' LEFT JOIN `xdf_video` ON `xdf_video`.`id` = `xdf_period`.`video_id`
					  LEFT JOIN `xdf_homework_period` ON `xdf_homework_period`.`period_id` = `xdf_period`.`id`
					  AND `xdf_homework_period`.`publish_type` = 3
					  LEFT JOIN `xdf_homework` ON `xdf_homework`.`id` = `xdf_homework_period`.`homework_id`';
		}
		
		$filter = ' WHERE TRUE';
		
		if($start_date){
			$filter.= ' AND UNIX_TIMESTAMP(`xdf_period`.`date`)>= UNIX_TIMESTAMP("'.$start_date.'")';
		}
		if($end_date){
			$filter.= ' AND UNIX_TIMESTAMP(`xdf_period`.`date`)< UNIX_TIMESTAMP("'.$end_date.'")';
		}
		if($class_id){
			$filter.= ' AND `xdf_class`.`id` = '.$class_id;
		}
		if($course){
			$filter.= ' AND `xdf_period`.`course` = "'.$course.'"';
		}
		if($teacher_id){
			$filter.= ' AND `xdf_period`.`teacher_id` = '.$teacher_id;
		}
		if($order){
			$order = ' ORDER BY `xdf_period`.`date`,`xdf_period`.`start_time`';
		}else{
			$order = ' ORDER BY `xdf_period`.`date` DESC,`xdf_period`.`start_time` DESC';
		}
		if($PageSize){
			$limit = ' LIMIT '.$ListNum.','.$PageSize.'';
		}else{
			$limit = '';
		}
		//echo($sql.$filter.$order.$limit.'<br/>');
		$result = $this->fetchAll($sql.$filter.$order.$limit);
		return $result;
	}
	
	/*教师查找课时
	 * 
	 * type : 0-仅包含课时信息,1-包含课时对应视频信息,2-包含课时对应作业信息,
	 * 			3-包含课时对应笔记信息,4-包含课时对应备课信息
	 */
	public function searchTeaPeriod($PageSize,$ListNum,$tea_id,$type,$start_date,$end_date,
					$publish_type = -1,$class_code = null,$course = null,$stuId = null,$order = 0){
		$sql = 'SELECT `xdf_period`.`id` AS periodId,`date`,`start_time`,`end_time`,
				`xdf_class`.`id` AS `classId`,`xdf_class`.`code` AS classCode,
				`xdf_class`.`name` AS className,`xdf_class`.`section_amount`,`section`,`course`';
		if($type == 1){//获取视频
			$sql .= ',`xdf_video`.`id` AS videoId';
			$sql .= ',`xdf_note`.`id` AS noteId,`xdf_note`.`amount`';
		}else if($type == 2){//获取作业
			$sql .= ',`xdf_homework`.`id` AS homeworkId,`xdf_homework`.`create_time`,
					`xdf_homework_period`.`publish_time`,`xdf_homework_period`.`publish_type`,
					`xdf_homework`.`item_num`';
		}else if($type == 3){//获取笔记
			$sql .= ',`xdf_note`.`id` AS noteId,`xdf_note`.`amount`';
		}else if($type == 4){//获取备课
			$sql .= ',`xdf_prepare`.`id` AS prepareId,`xdf_prepare`.`item_num`';
		}
		else{
			$sql .= ',`xdf_homework`.`id` AS homeworkId,`xdf_video`.`id` AS videoId,
					`xdf_video`.`name` AS videoName,`xdf_homework`.`name` AS homeworkName,
					`xdf_note`.`id` AS noteId,`xdf_prepare`.`id` AS prepareId,
					IFNULL(`xdf_prepare`.`item_num`,0) AS prepareNum,`xdf_homework_period`.`publish_type`';
		}
		
		$sql .= ' FROM `xdf_period` 
				LEFT JOIN `xdf_class` ON `xdf_class`.`id` = `xdf_period`.`class_id` ';
		
		if($type == 1){
			$sql .= ' INNER JOIN `xdf_video` ON `xdf_video`.`id` = `xdf_period`.`video_id`';
			$sql .= ' INNER JOIN `xdf_note` ON `xdf_note`.`id` = `xdf_period`.`note_id`';
		}else if($type == 2){
			$sql .= ' INNER JOIN `xdf_homework_period` ON `xdf_homework_period`.`period_id` = `xdf_period`.`id`
					  INNER JOIN `xdf_homework` ON `xdf_homework`.`id` = `xdf_homework_period`.`homework_id`';
		}else if($type == 3){
			$sql .= ' INNER JOIN `xdf_note` ON `xdf_note`.`id` = `xdf_period`.`note_id`';				
		}else if($type == 4){
			$sql .= ' INNER JOIN `xdf_prepare` ON `xdf_prepare`.`period_id` = `xdf_period`.`id`';
		}
		else {
			$sql .= ' LEFT JOIN `xdf_video` ON `xdf_video`.`id` = `xdf_period`.`video_id`
					  LEFT JOIN `xdf_homework_period` ON `xdf_homework_period`.`period_id` = `xdf_period`.`id`
					  LEFT JOIN `xdf_homework` ON `xdf_homework`.`id` = `xdf_homework_period`.`homework_id`
					  LEFT JOIN `xdf_note` ON `xdf_note`.`id` = `xdf_period`.`note_id`
					  LEFT JOIN `xdf_prepare` ON `xdf_prepare`.`period_id` = `xdf_period`.`id`';
		}
		
		$filter = ' WHERE `xdf_period`.`teacher_id` = "'.$tea_id.'"';
		
		if($start_date){
			$filter.= ' AND UNIX_TIMESTAMP(`xdf_period`.`date`)>= UNIX_TIMESTAMP("'.$start_date.'")';
		}
		if($end_date){
			$filter.= ' AND UNIX_TIMESTAMP(`xdf_period`.`date`)< UNIX_TIMESTAMP("'.$end_date.'")';
		}
		if($publish_type >= 0){
			$filter.= ' AND `xdf_homework_period`.`publish_type` = '.$publish_type;
		}
		if($course){
			$filter.= ' AND `xdf_period`.`course` = "'.$course.'"';
		}
		if($class_code){
			$filter.= ' AND `xdf_period`.`class_id` IN (SELECT id FROM `xdf_class` WHERE code like "%'.$class_code.'%")';
		}
		if($stuId){
			$filter.= ' AND `xdf_period`.`class_id` IN 
					(SELECT `class_id` FROM `xdf_class_student` WHERE `student_id` = '.$stuId.')';
		}
		if($order){
			$order = ' ORDER BY `xdf_period`.`date`,`xdf_period`.`start_time`';
		}else{
			$order = ' ORDER BY `xdf_period`.`date` DESC,`xdf_period`.`start_time` DESC';
		}
		if($PageSize){
			$limit = ' LIMIT '.$ListNum.','.$PageSize.'';
		}else{
			$limit = '';
		}
		//echo($sql.$filter.$order.$limit.'<br/>');
		$result = $this->fetchAll($sql.$filter.$order.$limit);
		return $result;
	}
	
	public function getStudentList($user_id){
		$sql = 'SELECT DISTINCT(U.`id`)  AS stuId,U.`name` FROM `xdf_user` AS U
				LEFT JOIN `xdf_class_student` AS CS ON U.`id` = CS.`student_id`
				 WHERE CS.`class_id` IN
				(SELECT `class_id` FROM `xdf_period` WHERE `teacher_id` = '.$user_id.')';
		return $this->fetchAll($sql);
	}
	
	public function getCourseList($teacher_id = null,$student_id = null){
		$sql = 'SELECT DISTINCT(course) FROM `xdf_period`';
		if($teacher_id){
			$filter = ' WHERE teacher_id = '.$teacher_id;
		}else if($student_id){
			$filter = ' WHERE class_id IN 
			(SELECT class_id FROM `xdf_class_student` WHERE student_id = '.$student_id.')';
		}
		return $this->fetchAll($sql.$filter);
	}
	
	public function getClassList($teacher_id){
		$sql = 'SELECT DISTINCT(class_id) FROM `xdf_period` 
				WHERE `teacher_id` = '.$teacher_id.' ORDER BY id';
		return $this->fetchAll($sql);
	}
	
	function getMyPeriodTotal($user_id){
		$sql="SELECT COUNT(`id`) as num FROM `xdf_period` WHERE `class_id` IN (
				SELECT `class_id` FROM `xdf_class_student` WHERE `student_id`= '$user_id' );";
		$row=$this->fetchRow($sql);
		return $row["num"];
	}
	function getDonePeriodTotal($user_id){
		$sql="SELECT COUNT(`id`) as num FROM `xdf_period` WHERE `class_id` IN (
				SELECT `class_id` FROM `xdf_class_student` WHERE `student_id`= '$user_id' ) AND `date` < NOW();";
		$row=$this->fetchRow($sql);
		return $row["num"];
	}
	function addPeriodvideo($period_id,$video_id,$note_id){
			$sql="UPDATE `xdf_period` SET `video_id`='$video_id',`note_id`=$note_id WHERE `id`='$period_id'";
			return $this->update($sql);
		
	}
	function getStudentIdByPeriod($period_id){
		$sql="SELECT student_id FROM `xdf_class_student` 
		WHERE class_id IN (SELECT class_id FROM `xdf_period` WHERE `id`='$period_id');";
		return $this->fetchAll($sql);
	}
	
}